﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using MySql.Data;
using System.Data;

/// <summary>
/// Summary description for DatabaseHandler
/// </summary>
public class DatabaseHandler
{
    public MySqlConnection conn;
    MySqlCommand command;
    DataTable table;
    DataSet ds;
    MySqlDataAdapter adapter;



    //public static string strConnect = @"Server=localhost;Database=test;Port=3306;UserID=;Password=;charset=utf8";
    public static string strConnect = @"Server=123.30.210.85;Database=ecithanh_dulieu;Port=3306;UserID=ecithanh_dulieu;Password=hoang123;charset=utf8";
    public DatabaseHandler()
	{
        conn = new MySqlConnection(strConnect);
	}

    public void connect()
    {

        if (conn.State != System.Data.ConnectionState.Open)
            conn.Open();
    }
    public void close()
    {
        if (conn.State == System.Data.ConnectionState.Open)
            conn.Close();
    }

    public bool insertIDDN(string IDDN, string tblName)
    {
        try
        {
            string sql = "INSERT INTO " + tblName + "(IDDN) VALUES('" + IDDN + "')";
            command = new MySqlCommand(sql, conn);
            return command.ExecuteNonQuery() > 0;
        }
        catch (MySqlException x)
        {
            
        }
        return false;
    }

    public bool CapNhatChiSo(string IDDN, string tblName, string column, string value)
    {
    
        string sql = "UPDATE " + tblName + " SET " + column + "='" + value + "' WHERE IDDN='" + IDDN + "'";
        try
        {
            command = new MySqlCommand(sql, conn);
            return command.ExecuteNonQuery() > 0;
        }
        catch (MySqlException x)
        {

        }

        return false;

    }

    public bool ThemDuLieu(string sqlQuery)
    {
        try
        {
            command = new MySqlCommand(sqlQuery, conn);
            return command.ExecuteNonQuery() > 0;
        }
        catch (MySqlException x)
        {

        }
        return false;
    }
    public DataTable loadAllData(String sql)
    {
        adapter = new MySqlDataAdapter(sql, conn);
        ds = new DataSet();
        adapter.Fill(ds, "tbl");
        return ds.Tables["tbl"];
    }
    public DataTable loadAllInforByIDDN(string IDDN,string tbl_name)
    {
        try
        {
            string sql = "SELECT * FROM " + tbl_name + " WHERE IDDN='" + IDDN + "'";
            adapter = new MySqlDataAdapter(sql, conn);
            ds = new DataSet();
            adapter.Fill(ds, "tbl_chiso");
            return ds.Tables["tbl_chiso"];
        }
        catch (MySqlException e)
        {
        }
        return null;
    }

    public MySqlDataReader dataReader(string sql)
    {

        command=new MySqlCommand(sql,conn);
        return command.ExecuteReader(); 
    }

    public bool delete(string tableName,string columnName,string where)
    {
        string query="DELETE FROM "+tableName+" WHERE "+columnName+"='"+where+"'";
        try
        {
            command = new MySqlCommand(query, conn);
            command.ExecuteNonQuery();
            return true;
        }
        catch (MySqlException sqlEx)
        {
            System.Windows.Forms.MessageBox.Show("Error during try to delete a record");
            return false;
        }

        return false;
    }
    
}